# -*- coding: gb2312 -*-
import os, types, datetime
import getpass
import thread, threading
import cx_Oracle

file_tns = 'tnsnames.ora'
file_output_dir='db_audit_result'
comma = ', '
sql='''SELECT userid AS db_user, SUBSTR(comment$text, 74, INSTR(comment$text, ')(', 74, 1) - 74) AS ip_address, 
       SUBSTR(comment$text,INSTR(comment$text, 'PORT=', 74, 1) + 5,  INSTR(comment$text, ')', 74, 2)-(INSTR(comment$text, 'PORT=', 74, 1) + 5) ) AS port,
       spare1 AS os_user, logoff$time as logoff_time
  FROM sys.aud$
 WHERE logoff$time > (sysdate - 7) and ((comment$text LIKE '%HOST=172.19.17.%') OR (comment$text LIKE '%HOST=172.17.%')) AND userid not in ('SYS', 'SYSTEM', 'BJ_STAT')
'''

sql=''''''
createDir = lambda dirname: not os.path.exists(dirname) and os.mkdir(dirname)
list_thread = []
lock_print = threading.Lock()
lock_file = threading.Lock()

class SQLThread(threading.Thread):
    def __init__(self, tns_str):
        self.tns = tns_str
        self.db_name = (self.tns.split('|'))[0].strip()
        self.fd_output = open(file_output_dir + '/' +  self.db_name + '.csv', 'w')
        print '[I]Thread ', self.db_name,' has started.'
        super(SQLThread, self).__init__(name = self.db_name)  

    def run(self):   
        if (self.tns.split('|'))[1].strip() == '' :
            print '[E]Invalid tns string in ', self.name
            thread.exit_thread()
        try :                 
            self.db_conn = cx_Oracle.connect(user,passwd,(self.tns.split('|'))[1].strip());
        except :
            print '[E]', self.db_name, ' gets an E in connectting database.'
            thread.exit_thread()
        self.cursor = self.db_conn.cursor()
        try:
            self.cursor.execute(sql)
        except :
            print '[E]', self.name, ' gets an E in executing sql in database.'
            thread.exit_thread()
        for row in self.cursor:
            row_str = ''
            for value in row:
                if type(value) is types.IntType or type(value) is types.FloatType or type(value) is types.LongType:
                    value = str(value)
                elif type(value) is types.NoneType :
                    value = ''
                elif type(value) is datetime.datetime :
                    value = str(value) 
                row_str = row_str +  value + comma
                
            lock_file.acquire()
            try :                    
                self.fd_output.writelines(row_str.strip()+'\n')
            except :
                print '[E]', self.name, ' get an error in writing " ', row_str, ' " to file ' , file_output_dir + '/' +  self.db_name + '.csv'
            finally :
                lock_file.release()
            #END of loop
        
        #close db connection
        self.fd_output.close()
        print '[I]Thread ', self.db_name,' has successfully completed.'
        self.db_conn.close()

if __name__ == '__main__':
    user = "bj_stat"
    passwd =  getpass.getpass('Please input the password of ' + user + ':')
    createDir(file_output_dir)
    
    #start thread
    print '--------------------------------------------------------------------'
    fd_tns = open(file_tns, mode='r', buffering=-1)
    #create thread under valuation conditions
    for tns_line in fd_tns:
        #ingore line beginning with '#'
        if tns_line.strip() == '' or (tns_line.strip())[0] == '#':
            continue
        list_thread.append(SQLThread(tns_line))
    fd_tns.close()
        
    #start threads
    for t in list_thread:
        t.start()
    
    for t in list_thread:
        t.join()
    #close all file discriptions
